﻿Imports System.Data.Objects
Imports System.IO
Imports System.Data
Imports VSModel
Imports Cls_Common

Partial Class Control_Product_ImportExcel
    Inherits System.Web.UI.UserControl
    Private Shared ReadOnly log As log4net.ILog = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType)
#Region "PRIVATE FUNCTION"
    Private Sub RemoveEmptyRow(ByRef dt1 As DataTable)
        'Remove đi nhưng dòng dt1
        Dim _dt1 = dt1.Rows.Cast(Of DataRow)().Where(
         Function(row) Not row.ItemArray.All(
          Function(field) TypeOf field Is System.DBNull _
           OrElse String.Compare(TryCast(field, String).Trim(), String.Empty) = 0) _
          ).CopyToDataTable()
        dt1 = _dt1
        _dt1.Dispose()
    End Sub
#End Region
#Region "EVENT FOR CONTROL"
    Protected Sub btnProcess_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnProcess.Click
        If flDocument.HasFile Then
            'To save every file with different name
            Dim randamName As String = DateTime.Now.ToFileTime().ToString()
            Dim relativePath As String = Server.MapPath("~") & "\Uploads\"
            If Not Directory.Exists(relativePath) Then
                Directory.CreateDirectory(relativePath)
            End If
            'Complete path of the file.
            Dim FilePath As String = (relativePath & randamName) + flDocument.FileName
            Try
                ' tiến hành upload file lên server
                flDocument.SaveAs(FilePath)
                '' Get Data from file 
                Dim dt As DataTable = New DataTable()
                dt = ReadExcelToDT(FilePath, 0, 0, "", 2)
                RemoveEmptyRow(dt1:=dt)
                Dim _totalRow As Integer = dt.Rows.Count
                Dim _rowEffect As Integer = 0
                If dt.Rows.Count > 0 Then
                    Using data As New VSEntities
                        For index = 0 To dt.Rows.Count - 1
                            Dim _ProductCode As String = ""
                            Try

                                If Not checkExistBatchNo(dt.Rows(index)(0).ToString) Then
                                    Dim wh As New Product
                                    With wh
                                        .Code = dt.Rows(index)(0).ToString
                                        .TongSo = dt.Rows(index)(1).ToString
                                        .Title = dt.Rows(index)(0).ToString
                                        .OriginalPrice = Right(dt.Rows(index)(0).ToString, 3) & "000"
                                        .SellPrice = Right(dt.Rows(index)(0).ToString, 3) & "000"
                                        .TinhTrangHang = 0 'con hang
                                        .StatusId = 1
                                        .Created = Now
                                        .CreatorId = Session("UserId")
                                    End With
                                    'loai nhung item trung batch no va ko co item va vendor
                                    If dt.Rows(index)(0).ToString <> "" AndAlso dt.Rows(index)(1).ToString <> "" Then
                                        data.Products.AddObject(wh)
                                        data.SaveChanges()
                                        _ProductCode = wh.Code
                                        _rowEffect = _rowEffect + 1
                                    End If
                                Else
                                    Dim _code As String = dt.Rows(index)(0).ToString
                                    Dim wh = (From q In data.Products Where q.Code = _code).FirstOrDefault
                                    wh.OriginalPrice = dt.Rows(index)(1).ToString
                                    wh.OriginalPrice = Right(dt.Rows(index)(0).ToString, 3) & "000"
                                    data.SaveChanges()
                                    _ProductCode = wh.Code
                                End If

                                'update here
                            Catch ex As Exception
                            End Try
                            'xu ly quota here
                            'shop 1
                            If dt.Rows(index)(2) <> "" Then
                                InsertQuota(16, _ProductCode, dt.Rows(index)(2), "CN1")
                            End If

                            'shop 2
                            If dt.Rows(index)(3) <> "" Then
                                InsertQuota(15, _ProductCode, dt.Rows(index)(3), "CN2")
                            End If

                            'shop 3
                            If dt.Rows(index)(4) <> "" Then
                                InsertQuota(17, _ProductCode, dt.Rows(index)(4), "CN3")
                            End If

                        Next
                    End Using
                End If
                Excute_Javascript(String.Format("AlertboxRedirect('Insert successful {0}/{1} rows.','../../Page/Product/List.aspx');", _rowEffect, _totalRow), Me.Page, True)
            Catch ex As Exception
                log4net.Config.XmlConfigurator.Configure()
                log.Error("Error error " & AddTabSpace(1) & Session("Username") & AddTabSpace(1) & "IP:" & GetIPAddress(), ex)
                txtFileUploadName.Text = ""
                Excute_Javascript("Alertbox('error while processing data !');", Me.Page, True)
            Finally
                '' final-> Delete file upload
                If File.Exists(FilePath) Then
                    File.Delete(FilePath)
                End If
            End Try
        End If
    End Sub


    Private Sub InsertQuota(ByVal _StoreId As Integer, ByVal _Code As String, ByVal Number As Integer, ByVal StoreName As String)
        If Number = 0 Then
            Exit Sub
        End If

        Using data As New VSEntities
            Dim _quota = (From q In data.Quotas Where q.StoreId = _StoreId And q.ProductCode = _Code Select q).FirstOrDefault
            If _quota Is Nothing Then
                Dim p As New VSModel.Quota
                p.QuotaNumber = StoreName & "-" & Now.Date.ToString("yyyy-MM-dd") & "-" & _Code
                p.StoreId = _StoreId
                p.Total = Number
                p.ProductCode = _Code
                p.Created = Now
                p.Creator = Session("Username")
                data.Quotas.AddObject(p)
                data.SaveChanges()
            End If
        End Using
    End Sub
    Function checkExistBatchNo(ByVal _BatchNo As String) As Boolean
        Using data As New VSEntities
            Dim p = (From q In data.Products Where q.Code = _BatchNo Select q).FirstOrDefault
            If Not p Is Nothing Then
                Return True
            End If
        End Using
        Return False
    End Function

#End Region

#Region "Apose read"
    Public Function ReadExcelToDT(ByVal strFilePath As String, ByVal intBeginRow As Integer, ByVal intBeginCol As Integer, ByRef strErr As String, Optional ByVal iRowEndVal As Integer = 0, Optional ByVal iColEndVal As Integer = 0) As DataTable
        If Not File.Exists(strFilePath) Then
            strErr = "FileNotFound"
        End If
        Dim wb As New Aspose.Cells.Workbook()
        wb.Open(strFilePath)
        Dim ws As Aspose.Cells.Worksheet = wb.Worksheets(0)

        Dim dt As New DataTable()
        '------init row,col-------------------------
        Dim iRowEnd As Integer = iRowEndVal
        Dim iColEnd As Integer = iColEndVal
        'If iRowEndVal > 0 Then
        iRowEnd = GetEndRow(intBeginRow, intBeginCol, ws)
        'End If
        ' If iColEndVal > 0 Then
        iColEnd = GetEndCol(intBeginRow, intBeginCol, ws)
        'End If
        '-------------------------------------------
        Try
            'get Header
            For j As Integer = intBeginCol To iColEnd - 1
                Dim strDataColumn As String = ws.Cells(intBeginRow, j).Value.ToString().Trim()
                dt.Columns.Add(New DataColumn(strDataColumn, GetType(String)))
            Next
            'get Data
            Dim intStep As Integer = 0
            For i As Integer = intBeginRow + 1 To iRowEnd - 1
                intStep = 0
                Dim dr As DataRow = dt.NewRow()
                For j As Integer = intBeginCol To iColEnd - 1
                    Dim strValue As String = If(ws.Cells(i, j).Value Is Nothing, "", ws.Cells(i, j).Value.ToString())
                    dr(intStep) = strValue
                    intStep += 1
                Next
                dt.Rows.Add(dr)
            Next
            Return dt
        Catch exp As Exception
            Return Nothing
        End Try
    End Function
    Public Function GetEndCol(ByVal intBeginRow As Integer, ByVal intBeginCol As Integer, ByVal ws As Aspose.Cells.Worksheet) As Integer
        Try
            Dim intCol As Integer = intBeginCol
            While True
                If ws.Cells(intBeginRow, intCol).Value Is Nothing _
                  OrElse ws.Cells(intBeginRow, intCol).Value.ToString() = "" Then
                    Exit While
                Else
                    intCol += 1
                End If
            End While
            Return intCol
        Catch ex As Exception
            Return 0
        End Try
    End Function
    Public Function GetEndRow(ByVal intBeginRow As Integer, ByVal intBeginCol As Integer, ByVal ws As Aspose.Cells.Worksheet) As Integer
        Try
            Dim intRow As Integer = intBeginRow + 1 ' +1 --> bo Header
            While True
                If ws.Cells(intRow, intBeginCol).Value Is Nothing _
                 OrElse ws.Cells(intRow, intBeginCol).Value.ToString() = "" Then
                    Exit While
                Else
                    intRow += 1
                End If
            End While
            Return intRow
        Catch ex As Exception
            Return 0
        End Try
    End Function
#End Region

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            hplFileTemplate.NavigateUrl = MainDomain & "Template/Template.xlsx"
        End If
    End Sub

End Class
